﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class BunkerInventoryService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        VesselService m_vesselService;
        GradeAccountMappingService m_gradeAccountMappingService;
        ProfitCentreService m_profitCentreService;
        ChargeTypeAccountMappingService m_ChargeTypeAccountMappingService;
        AlgBatcInputService m_AlgBatcInputService;
        ConfigurationSettingService m_configSettingService;
        ud_voyageimp_Service m_ud_voyageimp_Service;
        AstwarehouseService m_AstwarehouseService;
        ResponsibleUserService m_ResponsibleUserService;
        BunkerInventoryManagerMappingService m_BunkerInventoryManagerMappingService;

        public BunkerInventoryService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_profitCentreService = new ProfitCentreService();
            m_vesselService = new VesselService();
            m_gradeAccountMappingService = new GradeAccountMappingService();
            m_AlgBatcInputService = new AlgBatcInputService();
            m_ChargeTypeAccountMappingService = new ChargeTypeAccountMappingService();
            m_configSettingService = new ConfigurationSettingService();
            m_ud_voyageimp_Service = new ud_voyageimp_Service();
            m_AstwarehouseService = new AstwarehouseService();
            m_ResponsibleUserService = new ResponsibleUserService();
            m_BunkerInventoryManagerMappingService = new BunkerInventoryManagerMappingService();
        }
        public void ImportAllDataSoftmarToMiddleLayerBODandBOR()
        {
            try
            {
                IList bis = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllBunkerInventoryBODandBOR();
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerInventory po = new BunkerInventory(rd, false);
                    bis.Add(po);
                }
                rd.Close();

                //--------------------ADDD more by Get Bunker Enquiry----
                IList poList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllImportedBunkerInventory();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    poList.Add(BunkerInventory.CreateBunkerInventoryForComparation(rd));
                }
                rd.Close();


                foreach (BunkerInventory ci in bis)
                {
                      cmd.Parameters.Clear();
                      if (!poList.Contains(ci))
                      {
                          cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                          cmd.Parameters.Clear();
                          cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                          cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                          cmd.Parameters.Add("@headfixture_name", SqlDbType.NVarChar).Value = ci.headfixture_name;
                          // cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                          cmd.Parameters.Add("@quality", SqlDbType.NVarChar).Value = ci.quality;
                          // cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                          cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = ci.description;
                          //cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                          cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = ci.type;
                          // cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                          //cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                          //cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;.
                          cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                          cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                          cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                          cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                          cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                          cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;

                          cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                          cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                          cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                          cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = ci.vessel_code;
                          cmd.Parameters.Add("@end_voyage_date", SqlDbType.DateTime).Value = ci.end_voyage_date;
                          cmd.CommandText = BunkerInventorySQL.InsertBunkerInvTemp();
                          cmd.ExecuteNonQuery();
                      }


                   // cmd.CommandText = BunkerInventorySQL.CountBunkerInvTemp();
                   // cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                   // cmd.Parameters.Clear();
                   // cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                   // cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                   // cmd.Parameters.Add("@headfixture_name", SqlDbType.NVarChar).Value = ci.headfixture_name;
                   //// cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                   // cmd.Parameters.Add("@quality", SqlDbType.NVarChar).Value = ci.quality;
                   //// cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                   // cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = ci.description;
                   // //cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                   // cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = ci.type;
                   //// cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                   // //cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                   // //cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;
                   // int found = Convert.ToInt16(cmd.ExecuteScalar());
                   // if (found == 0)
                   // {
                   //     cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                   //     cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                   //     cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                   //     cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                   //     cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                   //     cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;

                   //     cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                   //     cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                   //     cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                   //     cmd.CommandText = BunkerInventorySQL.InsertBunkerInvTemp();
                   //     cmd.ExecuteNonQuery();
                   // }
                    //else
                    //{
                    //    cmd.CommandText = BunkerInventorySQL.UpdateBunkerInvVoyageStatus();
                    //    cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                    //    cmd.ExecuteNonQuery();
                    //}
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ImportAllDataSoftmarToMiddleLayerTotalConsumption()
        {
            try
            {
                IList bis = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllBunkerInventoryTotalConsumption();
                //DateTime test = new DateTime(2014, 3, 17, 15, 15, 15);
                cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = DateTime.Today.Date.Subtract(new TimeSpan(5, 0, 0, 0, 0)); //test.Date.Subtract(new TimeSpan(5, 0, 0, 0, 0));//
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerInventory po = new BunkerInventory(rd, false);
                    bis.Add(po);
                }
                rd.Close();

                if (bis.Count == 0) return;
                //--------------------ADDD more by Get Bunker Enquiry----
                IList poList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllImportedBunkerInventory();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    poList.Add(BunkerInventory.CreateBunkerInventoryForComparation(rd));
                }
                rd.Close();


                foreach (BunkerInventory ci in bis)
                {
                      cmd.Parameters.Clear();
                      if (!poList.Contains(ci))
                      {
                          cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                          cmd.Parameters.Clear();
                          cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                          cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                          cmd.Parameters.Add("@headfixture_name", SqlDbType.NVarChar).Value = ci.headfixture_name;
                          // cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                          cmd.Parameters.Add("@quality", SqlDbType.NVarChar).Value = ci.quality;
                          // cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                          cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = ci.description;
                          //cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                          cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = ci.type;
                          // cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                          //cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                          //cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;.
                          cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                          cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                          cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                          cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                          cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                          cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;

                          cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                          cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                          cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                          cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = ci.vessel_code;
                          cmd.Parameters.Add("@end_voyage_date", SqlDbType.DateTime).Value = ci.end_voyage_date;
                          cmd.CommandText = BunkerInventorySQL.InsertBunkerInvTemp();
                          cmd.ExecuteNonQuery();
                      }


                   // cmd.CommandText = BunkerInventorySQL.CountBunkerInvTemp();
                   // cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                   // cmd.Parameters.Clear();
                   // cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                   // cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                   // cmd.Parameters.Add("@headfixture_name", SqlDbType.NVarChar).Value = ci.headfixture_name;
                   //// cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                   // cmd.Parameters.Add("@quality", SqlDbType.NVarChar).Value = ci.quality;
                   //// cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                   // cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = ci.description;
                   // //cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                   // cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = ci.type;
                   //// cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                   // //cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                   // //cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;
                   // int found = Convert.ToInt16(cmd.ExecuteScalar());
                   // if (found == 0)
                   // {
                   //     cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                   //     cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                   //     cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                   //     cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                   //     cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                   //     cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;

                   //     cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                   //     cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                   //     cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                   //     cmd.CommandText = BunkerInventorySQL.InsertBunkerInvTemp();
                   //     cmd.ExecuteNonQuery();
                   // }
                    //else
                    //{
                    //    cmd.CommandText = BunkerInventorySQL.UpdateBunkerInvVoyageStatus();
                    //    cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                    //    cmd.ExecuteNonQuery();
                    //}
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }


        public int ImportAllDataSoftmarToMiddleLayerMigration(string VoyageNo, string VesselName, string description)
        {
            try
            {
                IList bis = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllBunkerInventoryMigration();
                cmd.Parameters.Add("@VoyageNo", SqlDbType.Int).Value = Convert.ToInt32(VoyageNo);
                cmd.Parameters.Add("@VesselName", SqlDbType.VarChar).Value = VesselName;
                cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = description;
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerInventory po = new BunkerInventory(rd, false);
                    bis.Add(po);
                }
                rd.Close(); 
                if (bis.Count == 0) return 0;
                //--------------------ADDD more by Get Bunker Enquiry----
                IList poList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllImportedBunkerInventory();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    poList.Add(BunkerInventory.CreateBunkerInventoryForComparation(rd));
                }
                rd.Close();

                foreach (BunkerInventory ci in bis)
                {
                    cmd.Parameters.Clear();
                    if (!poList.Contains(ci))
                    {
                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@profit_centre_fullname", SqlDbType.NVarChar).Value = ci.profit_centre_fullname;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                        cmd.Parameters.Add("@headfixture_name", SqlDbType.NVarChar).Value = ci.headfixture_name;
                        cmd.Parameters.Add("@quality", SqlDbType.NVarChar).Value = ci.quality;
                        cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = ci.description;
                        cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = ci.type;
                        cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = ci.price;
                        cmd.Parameters.Add("@quantity", SqlDbType.Decimal).Value = ci.quantity;
                        cmd.Parameters.Add("@order_", SqlDbType.Int).Value = ci.order_;
                        cmd.Parameters.Add("@last_updated_by", SqlDbType.NVarChar).Value = ci.last_updated_by;
                        cmd.Parameters.Add("@last_updated_date", SqlDbType.DateTime).Value = ci.last_updated_date;
                        cmd.Parameters.Add("@operated_by", SqlDbType.NVarChar).Value = ci.operated_by;
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@VoyageStatus", SqlDbType.NVarChar).Value = ci.VoyageStatus;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = ci.vessel_code;
                        cmd.Parameters.Add("@end_voyage_date", SqlDbType.DateTime).Value = ci.end_voyage_date;
                        cmd.CommandText = BunkerInventorySQL.InsertBunkerInvTemp();
                        cmd.ExecuteNonQuery();
                    }
                }
                return bis.Count;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ExecuteInterfaceIWTotalConsumption()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList bunkerInvs = new ArrayList();
                SqlCommand cmd = new SqlCommand(BunkerInventorySQL.GetPendingAllBunkerInvTotalConsumption(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerInvs.Add(new BunkerInventory(rd, true));
                }
                rd.Close();
               // int test = 0;
                string batch_id_datetime = "";//same voyage same batch ID 19062013
                int voyage_no = 0;//same voyage same batch ID 19062013
                string headfixture = "";
                int line = 1;
                foreach (BunkerInventory bi in bunkerInvs)
                {
                    //  if (test == 20) break;
                    // if (!(bi.BunkerInventory_id == 17739 || bi.BunkerInventory_id == 17741))
                    //        || bi.BunkerInventory_id == 16111)
                    //  )
                    // continue;
                    if ((voyage_no != bi.voyage_no) || (headfixture != bi.headfixture_name))//same voyage same batch ID 19062013
                    {
                        batch_id_datetime = DateTime.Now.ToString("ddMMyyyyHHmmss");
                        voyage_no = bi.voyage_no;
                        headfixture = bi.headfixture_name;
                        line = 1;
                    }
                    else
                        line++;//20062013 same head fixtture and voyage , position continue.

                    ProfitCentre profitCentre = m_profitCentreService.GetProfitCentreMapping(bi.profit_centre_fullname);
                    m_vesselService.FindVesselByName(bi.headfixture_name, bi.vessel_code, bi.profit_centre_fullname);
                    Vessel profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, bi.headfixture_name, profitCentre.client);
                    GradeAccountMapping gam = m_gradeAccountMappingService.GetGradeAccountMapping(bi.quality);
                    ChargeTypeAccountMapping delAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Del Bunkers ROB");
                    ChargeTypeAccountMapping redelAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Redel Bunkers ROB");
                    //bool validVoyage = m_ud_voyageimp_Service.IsRegisteredVoyage(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bi.voyage_no.ToString());
                    afxvoyage afxvoyageVoyage = m_ud_voyageimp_Service.GetAFXVOYAGEByDimValue(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bi.voyage_no.ToString());
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(bi.operated_by);
                    // BunkerInventoryManagerMapping biMap = null;
                    //  if (profitCentre != null)
                    //  {
                    //  biMap = m_BunkerInventoryManagerMappingService.SearchInsert(bi.profit_centre_fullname,
                    //  profitCentre.client, profitCentre.agr_description, profitCentre.agr_dim_value, profitCentre.agr_rel_value);
                    //  }
                    string message = "";

                    if (profitCentre != null && profitCentre.client == "") { message += string.Format("-[E007] Unable to find client for Profit Centre '{0}' \r\n", bi.profit_centre_fullname); }
                    if (profitCentre.agr_dim_value == "") { message += string.Format("-[E009] Unable to find Dim_Val Code for Profit Centre '{0}' \r\n", bi.profit_centre_fullname); }
                    if (gam.account == "") { message += string.Format("-[E024] Unable to find account code Grade '{0}' \r\n", bi.quality); }
                    if (profitCentreVessel == null) { message += string.Format("-[E015] Unable to find Vessel '{0}' \r\n", bi.headfixture_name); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value == "") { message += string.Format("-[E001] Unable to find Dim_Val Code for Vessel '{0}' \r\n", bi.headfixture_name); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_rel_value == "") { message += string.Format("-[E025] Unable to find Vessel rel_value '{0}' \r\n", bi.headfixture_name); }
                    if (delAcc.chartererInv_account == "") { message += "- Unable to Account For Del Bunkers ROB \r\n"; }
                    if (redelAcc.chartererInv_account == "") { message += "- Unable to Account For Redel Bunkers ROB \r\n"; }

                    // if (biMap != null && (biMap.dim_value_map.Trim() == "" || biMap.rel_value_map.Trim() == "" || biMap.client_map.Trim() == "")) { message += "- Bunker Inventory Manager Mapping is not set \r\n"; }

                    if (afxvoyageVoyage == null)
                    {
                        message += string.Format("-[E016] Unable to Find Voyage in Voyage Master, Vessel: '{0}' Voyage: '{1}' Code:'{2}' \r\n", bi.headfixture_name.Trim(), bi.voyage_no
                        , (profitCentreVessel != null ? profitCentreVessel.agr_dim_value : "") + "-V" + bi.voyage_no.ToString().PadLeft(3, '0'))
                        ;
                    }
                    //warehouse validation..
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value != "")
                    {
                        bool registered = m_AstwarehouseService.IsRegisteredWarehouse(profitCentreVessel.agr_dim_value);
                        if (!registered)
                            message += string.Format("-[E018] Unable to find Warehouse Vessel '{0}'  \r\n", profitCentreVessel.agr_dim_value);
                    }
                    if (message != "")
                    {
                        UpdateStatusBunkerInv(bi.BunkerInventory_id, message, "0", bi.remark);
                        continue;
                    }

                    algbatchinput algbatch = new algbatchinput();
                    // if ((bi.description.ToLower() == "bunkers on delivery to tc") || (bi.description.ToLower() == "total consumption"))
                    //  {
                    algbatch.voucher_type = "IW";
                    algbatch.trans_type = "46";
                    algbatch.article = gam.account_grn;
                    algbatch.amount = 0;
                    algbatch.amount_set = 0;//27052013
                    algbatch.status = "";//27052013
                    algbatch.batch_id = "IW" + batch_id_datetime;// DateTime.Now.ToString("ddMMyyyyHHmmss");
                    algbatch.client = profitCentre.client;//09072013 biMap.client_map;//"S1";//09062013 profitCentre.client.Trim();
                    //algbatch.deliv_date = bi.EndVoyageDate;
                    algbatch.order_id = GetOrderRunningNumber();
                    algbatch.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));//Convert.ToInt32(bi.last_updated_date.ToString("yyyyMM"));
                    algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    algbatch.value_1 = Math.Round(bi.quantity, 4);
                    algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    algbatch.art_descr = bi.description;
                    algbatch.unit_code = "TON";
                    algbatch.unit_price = bi.price;
                    algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    algbatch.line_no = line;//20062013 1;

                    algbatch.account = bi.GetAccountCode(algbatch.article, delAcc.chartererInv_account, redelAcc.chartererInv_account);
                    algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    algbatch.dim_4 = profitCentreVessel.agr_dim_value + "-V" + bi.voyage_no.ToString().PadLeft(3, '0');
                    //algbatch.dim_4 = algbatch.account == redelAcc.freighthire_account ? "" : algbatch.dim_4;
                    algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    algbatch.dim_6 = profitCentre.agr_dim_value;
                    algbatch.delivery_descr = bi.description;
                    algbatch.deliv_date = afxvoyageVoyage.voy_actual_voy_end;

                    //if (algbatch.dim_6.Trim() != "610") //not pcl shipping
                    //{
                    //    algbatch.account = "2904";
                    //    algbatch.dim_1 = biMap.rel_value_map;//"D6100";
                    //    algbatch.dim_2 = bi.GetAccountCode(algbatch.article, delAcc.freighthire_account, redelAcc.freighthire_account);
                    //    algbatch.dim_6 = biMap.dim_value_map;//"610";
                    //    algbatch.dim_7 = profitCentre.agr_dim_value;
                    //}
                    //   }
                    //else 
                    //{
                    //    algbatch.voucher_type = "IR";
                    //    algbatch.trans_type = "49";
                    //    algbatch.article = gam.account_grn;
                    //    algbatch.amount = bi.quantity * bi.price;
                    //    algbatch.amount_set = 1;//27052013
                    //    algbatch.status = "";//27052013
                    //    algbatch.batch_id = "IR" + batch_id_datetime;//DateTime.Now.ToString("ddMMyyyyHHmmss");
                    //    algbatch.client = profitCentre.client;//09072013 biMap.client_map;// "S1";//09062013 profitCentre.client.Trim();
                    //    algbatch.deliv_date = bi.last_updated_date;
                    //    algbatch.order_id = GetOrderRunningNumber();
                    //    algbatch.period = Convert.ToInt32(bi.last_updated_date.ToString("yyyyMM"));
                    //    algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    //    algbatch.value_1 = Math.Round(bi.quantity, 4);
                    //    algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    //    algbatch.art_descr = bi.description;
                    //    algbatch.unit_code = "TON";
                    //    algbatch.unit_price = bi.price;
                    //    algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    //    algbatch.line_no = line;

                    //    algbatch.account = bi.GetAccountCode(algbatch.article, delAcc.freighthire_account, redelAcc.freighthire_account);
                    //    algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    //    algbatch.dim_4 = "";
                    //    algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    //    algbatch.dim_6 = profitCentre.agr_dim_value;
                    //    algbatch.delivery_descr = bi.description;
                    //    algbatch.deliv_date = afxvoyageVoyage.voy_actual_voy_end;

                    //    //if (algbatch.dim_6.Trim() != "610") //not pcl shipping
                    //    //{
                    //    //    algbatch.account = "2904";
                    //    //    algbatch.dim_1 = biMap.rel_value_map;//"D6100";
                    //    //    algbatch.dim_2 = bi.GetAccountCode(algbatch.article, delAcc.freighthire_account, redelAcc.freighthire_account);
                    //    //    algbatch.dim_6 = biMap.dim_value_map;//"610";
                    //    //    algbatch.dim_7 = profitCentre.agr_dim_value;
                    //    //}
                    //}

                    //algbatch.article = gam.account;
                    //algbatch.account = bi.GetAccountCode(algbatch.article, delAcc.freighthire_account, redelAcc.freighthire_account);
                    //algbatch.amount = bi.GetAmount();
                    //algbatch.amount_set = bi.GetVoucherType() == "IW" ? 0 : 1;//27052013
                    //algbatch.status = "";//27052013

                    //algbatch.batch_id = bi.GetVoucherType() + DateTime.Now.ToString("ddMMyyyyHHmmss");
                    //algbatch.client = "S1";//09062013 profitCentre.client.Trim();
                    //algbatch.deliv_date = bi.last_updated_date;
                    //algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    //algbatch.dim_4 = profitCentreVessel.agr_dim_value + "-V" + bi.voyage_no.ToString().PadLeft(3, '0');
                    //algbatch.dim_4 = algbatch.account == redelAcc.freighthire_account ? "" : algbatch.dim_4;
                    //algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    //algbatch.dim_6 = profitCentre.agr_dim_value;
                    //algbatch.line_no = 1;
                    //algbatch.order_id = GetOrderRunningNumber();
                    //algbatch.period = Convert.ToInt32(bi.last_updated_date.ToString("yyyyMM"));
                    //algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    //algbatch.trans_type = bi.GetTransactionType();
                    //algbatch.value_1 = Math.Round(bi.quantity, 4);
                    //algbatch.voucher_type = bi.GetVoucherType();
                    //algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    //algbatch.art_descr = bi.description;
                    //algbatch.unit_code = "TON";
                    //algbatch.unit_price = bi.price;
                    //algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    //// algbatch.status = "N";

                    m_AlgBatcInputService.Insert(algbatch);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatch);
                    UpdateStatusBunkerInv(bi.BunkerInventory_id, algbatch.batch_id + ";" + algbatch.agrtid.ToString(), "1", bi.remark);

                    //  test++;

                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ExecuteInterfaceIWBunkerOnDelivery()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList bunkerInvs = new ArrayList();
                SqlCommand cmd = new SqlCommand(BunkerInventorySQL.GetPendingAllBunkerInvBunkerOnDelivery(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerInvs.Add(new BunkerInventory(rd, true));
                }
                rd.Close();
                // int test = 0;
                string batch_id_datetime = "";//same voyage same batch ID 19062013
                int voyage_no = 0;//same voyage same batch ID 19062013
                string headfixture = "";
                int line = 1;
                foreach (BunkerInventory bi in bunkerInvs)
                {
                    //  if (test == 20) break;
                     //if (!(bi.BunkerInventory_id == 604 || bi.BunkerInventory_id == 606))
                     //continue;
                    if ((voyage_no != bi.voyage_no) || (headfixture != bi.headfixture_name))//same voyage same batch ID 19062013
                    {
                        batch_id_datetime = DateTime.Now.ToString("ddMMyyyyHHmmss");
                        voyage_no = bi.voyage_no;
                        headfixture = bi.headfixture_name;
                        line = 1;
                    }
                    else
                        line++;//20062013 same head fixtture and voyage , position continue.

                    ProfitCentre profitCentre = m_profitCentreService.GetProfitCentreMapping(bi.profit_centre_fullname);
                    m_vesselService.FindVesselByName(bi.headfixture_name, bi.vessel_code, bi.profit_centre_fullname);
                    Vessel profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, bi.headfixture_name, profitCentre.client);
                    GradeAccountMapping gam = m_gradeAccountMappingService.GetGradeAccountMapping(bi.quality);
                    ChargeTypeAccountMapping delAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Del Bunkers ROB");
                    ChargeTypeAccountMapping redelAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Redel Bunkers ROB");
                    //bool validVoyage = m_ud_voyageimp_Service.IsRegisteredVoyage(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bi.voyage_no.ToString());
                    afxvoyage afxvoyageVoyage = m_ud_voyageimp_Service.GetAFXVOYAGEByDimValue(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bi.voyage_no.ToString());
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(bi.operated_by);
                    // BunkerInventoryManagerMapping biMap = null;
                    //  if (profitCentre != null)
                    //  {
                    //  biMap = m_BunkerInventoryManagerMappingService.SearchInsert(bi.profit_centre_fullname,
                    //  profitCentre.client, profitCentre.agr_description, profitCentre.agr_dim_value, profitCentre.agr_rel_value);
                    //  }
                    string message = "";

                    if (profitCentre != null && profitCentre.client == "") { message += string.Format("-[E007] Unable to find client for Profit Centre '{0}' \r\n", bi.profit_centre_fullname); }
                    if (profitCentre.agr_dim_value == "") { message += string.Format("-[E009] Unable to find Dim_Val Code for Profit Centre '{0}' \r\n", bi.profit_centre_fullname); }
                    if (gam.account == "") { message += string.Format("-[E024] Unable to find account code Grade '{0}' \r\n", bi.quality); }
                    if (profitCentreVessel == null) { message += string.Format("-[E015] Unable to find Vessel '{0}' \r\n", bi.headfixture_name); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value == "") { message += string.Format("-[E001] Unable to find Dim_Val Code for Vessel '{0}' \r\n", bi.headfixture_name); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_rel_value == "") { message += string.Format("-[E025] Unable to find Vessel rel_value '{0}' \r\n", bi.headfixture_name); }
                    if (delAcc.chartererInv_account == "") { message += "- Unable to Account For Del Bunkers ROB \r\n"; }
                    if (redelAcc.chartererInv_account == "") { message += "- Unable to Account For Redel Bunkers ROB \r\n"; }

                    // if (biMap != null && (biMap.dim_value_map.Trim() == "" || biMap.rel_value_map.Trim() == "" || biMap.client_map.Trim() == "")) { message += "- Bunker Inventory Manager Mapping is not set \r\n"; }

                    if (afxvoyageVoyage == null)
                    {
                        message += string.Format("-[E016] Unable to Find Voyage in Voyage Master, Vessel: '{0}' Voyage: '{1}' Code:'{2}' \r\n", bi.headfixture_name.Trim(), bi.voyage_no
                        , (profitCentreVessel != null ? profitCentreVessel.agr_dim_value : "") + "-V" + bi.voyage_no.ToString().PadLeft(3, '0'))
                        ;
                    }
                    //warehouse validation..
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value != "")
                    {
                        bool registered = m_AstwarehouseService.IsRegisteredWarehouse(profitCentreVessel.agr_dim_value);
                        if (!registered)
                            message += string.Format("-[E018] Unable to find Warehouse Vessel '{0}'  \r\n", profitCentreVessel.agr_dim_value);
                    }
                    if (message != "")
                    {
                        UpdateStatusBunkerInv(bi.BunkerInventory_id, message, "0", bi.remark);
                        continue;
                    }

                    algbatchinput algbatch = new algbatchinput();
                    // if ((bi.description.ToLower() == "bunkers on delivery to tc") || (bi.description.ToLower() == "total consumption"))
                    //  {
                    algbatch.voucher_type = "IW";
                    algbatch.trans_type = "46";
                    algbatch.article = gam.account_grn;
                    algbatch.amount = 0;
                    algbatch.amount_set = 0;//27052013
                    algbatch.status = "";//27052013
                    algbatch.batch_id = "IW" + batch_id_datetime;// DateTime.Now.ToString("ddMMyyyyHHmmss");
                    algbatch.client = profitCentre.client;//09072013 biMap.client_map;//"S1";//09062013 profitCentre.client.Trim();
                    algbatch.deliv_date = bi.last_updated_date;
                    algbatch.order_id = GetOrderRunningNumber();
                    algbatch.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));//Convert.ToInt32(bi.last_updated_date.ToString("yyyyMM"));
                    algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    algbatch.value_1 = Math.Round(bi.quantity, 4);
                    algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    algbatch.art_descr = bi.description;
                    algbatch.unit_code = "TON";
                    algbatch.unit_price = bi.price;
                    algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    algbatch.line_no = line;//20062013 1;

                    algbatch.account = bi.GetAccountCode(algbatch.article, delAcc.chartererInv_account, redelAcc.chartererInv_account);
                    algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    algbatch.dim_4 = profitCentreVessel.agr_dim_value + "-V" + bi.voyage_no.ToString().PadLeft(3, '0');
                    //algbatch.dim_4 = algbatch.account == redelAcc.freighthire_account ? "" : algbatch.dim_4;
                    algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    algbatch.dim_6 = profitCentre.agr_dim_value;
                    algbatch.delivery_descr = bi.description;
                    algbatch.deliv_date = afxvoyageVoyage.voy_actual_voy_start;

                    m_AlgBatcInputService.Insert(algbatch);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatch);
                    UpdateStatusBunkerInv(bi.BunkerInventory_id, algbatch.batch_id + ";" + algbatch.agrtid.ToString(), "1", bi.remark);

                    //  test++;

                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ExecuteInterfaceIRBunkerOnReDelivery()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList bunkerInvs = new ArrayList();
                SqlCommand cmd = new SqlCommand(BunkerInventorySQL.GetPendingAllBunkerInvBunkerOnReDelivery(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    bunkerInvs.Add(new BunkerInventory(rd, true));
                rd.Close();
                string batch_id_datetime = "";//same voyage same batch ID 19062013
                int voyage_no = 0;//same voyage same batch ID 19062013
                string headfixture = "";
                string client = "";
                int line = 1;

                foreach (BunkerInventory bi in bunkerInvs)
                {
                    // if (!(bi.BunkerInventory_id == 17739 || bi.BunkerInventory_id == 17741))
                    // continue;
                    
                    if ((voyage_no != bi.voyage_no) || (headfixture != bi.headfixture_name))//same voyage same batch ID 19062013
                    {
                        batch_id_datetime = DateTime.Now.ToString("ddMMyyyyHHmmss");
                        voyage_no = bi.voyage_no;
                        headfixture = bi.headfixture_name;
                        line = 1;
                    }
                    else
                        line++;//20062013 same head fixtture and voyage , position continue.

                    ProfitCentre profitCentre = m_profitCentreService.GetProfitCentreMapping(bi.profit_centre_fullname);
                    m_vesselService.FindVesselByName(bi.headfixture_name, bi.vessel_code, bi.profit_centre_fullname);
                    Vessel profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, bi.headfixture_name, profitCentre.client);
                    GradeAccountMapping gam = m_gradeAccountMappingService.GetGradeAccountMapping(bi.quality);
                    ChargeTypeAccountMapping delAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Del Bunkers ROB");
                    ChargeTypeAccountMapping redelAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Redel Bunkers ROB");
                    afxvoyage afxvoyageVoyage = m_ud_voyageimp_Service.GetAFXVOYAGEByDimValue(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bi.voyage_no.ToString());
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(bi.operated_by);
                    string message = "";
                    if (profitCentre != null && profitCentre.client == "") { message += string.Format("-[E007] Unable to find client for Profit Centre '{0}' \r\n", bi.profit_centre_fullname); }
                    if (profitCentre.agr_dim_value == "") { message += string.Format("-[E009] Unable to find Dim_Val Code for Profit Centre '{0}' \r\n", bi.profit_centre_fullname); }
                    if (gam.account == "") { message += string.Format("-[E024] Unable to find account code Grade '{0}' \r\n", bi.quality); }
                    if (profitCentreVessel == null) { message += string.Format("-[E015] Unable to find Vessel '{0}' \r\n", bi.headfixture_name); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value == "") { message += string.Format("-[E001] Unable to find Dim_Val Code for Vessel '{0}' \r\n", bi.headfixture_name); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_rel_value == "") { message += string.Format("-[E025] Unable to find Vessel rel_value '{0}' \r\n", bi.headfixture_name); }
                    if (delAcc.chartererInv_account == "") { message += "- Unable to Account For Del Bunkers ROB \r\n"; }
                    if (redelAcc.chartererInv_account == "") { message += "- Unable to Account For Redel Bunkers ROB \r\n"; }

                    if (afxvoyageVoyage == null)
                    {
                        message += string.Format("-[E016] Unable to Find Voyage in Voyage Master, Vessel: '{0}' Voyage: '{1}' Code:'{2}' \r\n", bi.headfixture_name.Trim(), bi.voyage_no
                        , (profitCentreVessel != null ? profitCentreVessel.agr_dim_value : "") + "-V" + bi.voyage_no.ToString().PadLeft(3, '0'))
                        ;
                    }
                    //warehouse validation..
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value != "")
                    {
                        bool registered = m_AstwarehouseService.IsRegisteredWarehouse(profitCentreVessel.agr_dim_value);
                        if (!registered)
                            message += string.Format("-[E018] Unable to find Warehouse Vessel '{0}'  \r\n", profitCentreVessel.agr_dim_value);
                    }
                    if (message != "")
                    {
                        UpdateStatusBunkerInv(bi.BunkerInventory_id, message, "0", bi.remark);
                        continue;
                    }

                    if (client != "") // differnet client different batch
                    {
                        if (client != profitCentre.client)
                            batch_id_datetime = DateTime.Now.AddSeconds(1).ToString("ddMMyyyyHHmmss");
                    }
                    client = profitCentre.client; // differnet client different batch

                    algbatchinput algbatch = new algbatchinput();
                    algbatch.voucher_type = "IR";
                    algbatch.trans_type = "49";
                    algbatch.article = gam.account_grn;
                    algbatch.amount = bi.quantity * bi.price;
                    algbatch.amount_set = 1;//27052013
                    algbatch.status = "";//27052013
                    algbatch.batch_id = "IR" + batch_id_datetime;//DateTime.Now.ToString("ddMMyyyyHHmmss");
                    algbatch.client = profitCentre.client;//09072013 biMap.client_map;// "S1";//09062013 profitCentre.client.Trim();
                    algbatch.deliv_date = bi.last_updated_date;
                    algbatch.order_id = GetOrderRunningNumber();
                    algbatch.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));//Convert.ToInt32(bi.last_updated_date.ToString("yyyyMM"));
                    algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    algbatch.value_1 = Math.Round(bi.quantity, 4);
                    algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    algbatch.art_descr = bi.description;
                    algbatch.unit_code = "TON";
                    algbatch.unit_price = bi.price;
                    algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    algbatch.line_no = line;
                    algbatch.account = bi.GetAccountCode(algbatch.article, delAcc.chartererInv_account, redelAcc.chartererInv_account);
                    algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    algbatch.dim_4 = "";
                    algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    algbatch.dim_6 = profitCentre.agr_dim_value;
                    algbatch.delivery_descr = bi.description;
                    algbatch.deliv_date = afxvoyageVoyage.voy_actual_voy_end;

                    m_AlgBatcInputService.Insert(algbatch);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatch);
                    UpdateStatusBunkerInv(bi.BunkerInventory_id, algbatch.batch_id + ";" + algbatch.agrtid.ToString(), "1", bi.remark);
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private int GetOrderRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_3) + 1;
            runningNumber.val_3 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }

        private void UpdateStatusBunkerInv(int p, string remarks, string status, string oldRemarks)
        {
            if (status == "0")
                if (remarks == oldRemarks) return;
            m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
            SqlCommand cmd = new SqlCommand(BunkerInventorySQL.UpdateBunkerInvPickup_flagStatus(), m_dataBaseConectivity.InterfaceSqlConnection_c);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = status;
            cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@remark", SqlDbType.NVarChar).Value = remarks;
            cmd.Parameters.Add("@BunkerInventory_id", SqlDbType.Int).Value = p;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
        }

        public IList GetAllPendingBunkerInventory(string p)
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllPendingBunkerInventory(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new BunkerInventory(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetAllPendingBunkerInventory()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllPendingBunkerInventory();
                //cmd.CommandText = BunkerInventorySQL.GetAllPendingCloseBunkerInventory();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new BunkerInventory(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public int GetAllPendingBunkerInventoryCount()
        {
            int commissionInvoices = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerInventorySQL.GetAllPendingBunkerInventoryCount();
                commissionInvoices = Convert.ToInt32(cmd.ExecuteScalar());
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateVesselCode()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select * from i_BunkerInventory where pickup_flag != 0";
                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new BunkerInventory(rd, true));
                }
                rd.Close();
                foreach (BunkerInventory st in commissionInvoices)
                {
                    cmdSoftmar.CommandText = string.Format(@"Select distinct vVMVoyageSrch.Vessel vessel_code
                     FROM VVMVOYAGESRCH 
					 where vVMVoyageSrch.VesselName = '{0}' and vVMVoyageSrch.VoyageNo = {1}", st.headfixture_name, st.voyage_no);
                    string vessel_code = "";
                    SqlDataReader rdSf = cmdSoftmar.ExecuteReader();
                    while (rdSf.Read())
                    {
                        vessel_code = rdSf[0].ToString();
                    }
                    rdSf.Close();
                    cmd.CommandText = string.Format("update i_BunkerInventory set vessel_code = '{0}'  where BunkerInventory_id = {1}", vessel_code, st.BunkerInventory_id);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }

        public void UpdateEndVoyageDate()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select * from i_BunkerInventory";
                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new BunkerInventory(rd, true));
                }
                rd.Close();
                foreach (BunkerInventory st in commissionInvoices)
                {
                    cmdSoftmar.CommandText = string.Format(@"Select distinct vVMVoyageSrch.EndVoyageDate end_voyage_date
                     FROM VVMVOYAGESRCH 
					 where vVMVoyageSrch.VesselName = '{0}' and vVMVoyageSrch.VoyageNo = {1}  order by vVMVoyageSrch.EndVoyageDate", st.headfixture_name, st.voyage_no);
                    DateTime end_date = DateTime.Today;
                    SqlDataReader rdSf = cmdSoftmar.ExecuteReader();
                    while (rdSf.Read())
                    {
                        end_date = Convert.ToDateTime(rdSf[0]);
                    }
                    rdSf.Close();
                    cmd.CommandText = string.Format("update i_BunkerInventory set end_voyage_date = '{0}'  where BunkerInventory_id = {1}", end_date.ToString("yyyy-MM-dd HH:mm:ss"), st.BunkerInventory_id);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
    }
}
